package com.hotel.dao.impl;

import com.hotel.dao.FoodDao;
import com.hotel.pojo.Food;
import com.hotel.utils.Condition;
import com.hotel.utils.JdbcUtils;
import com.hotel.utils.PageBean;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by LinYaTing on 2018/11/25 20:45
 */
public class FoodDaoImpl implements FoodDao {

    @Override
    public List<Map<String, Object>> findAll() {
        String sql = "SELECT f.id,f.foodType_id foodType_id,f.foodName,ft.typeName,f.price,f.mPrice FROM food f , foodtype ft WHERE f.foodType_id = ft.id";
        try {
            return JdbcUtils.getQueryRunner().query(sql, new MapListHandler());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Map<String, Object>> findFoodByKeyWord(String keyWord) {
        String sql = "SELECT f.id,f.foodName,ft.typeName,f.price,f.mPrice FROM food f,foodtype ft WHERE f.foodType_id = ft.id AND CONCAT(ft.typeName,f.foodName) LIKE ?";
        try {
            return JdbcUtils.getQueryRunner().query(sql, new MapListHandler(), "%" + keyWord + "%");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Map<String, Object> findFoodById(int id) {
        String sql = "SELECT f.id,f.foodName,ft.typeName,f.price,f.mPrice,f.remark,f.img FROM food f,foodtype ft WHERE f.foodType_id = ft.id AND f.id = ?";
        try {
            return JdbcUtils.getQueryRunner().query(sql, new MapHandler(), id);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void save(Food food) {
        String sql  = "insert into food (foodName, foodType_id, price, mPrice, remark, img) values(?, ?, ?, ?, ?, ?)";
        try {
            JdbcUtils.getQueryRunner().update(sql, food.getFoodName(), food.getFoodType_id(), food.getPrice(), food.getmPrice(), food.getRemark(), food.getImg());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void update(Food food) {
        String sql = "update food set foodName = ?, foodType_id = ?, price = ?, mPrice = ?, remark = ?, img = ? where id = ?";
        try {
            JdbcUtils.getQueryRunner().update(sql, food.getFoodName(), food.getFoodType_id(), food.getPrice(), food.getmPrice(), food.getRemark(), food.getImg(), food.getId());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void delete(int id) {
        String sql = "delete from food where id = ?";
        try {
            JdbcUtils.getQueryRunner().update(sql, id);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public void getAll(PageBean<Food> pb) {
        // 获取条件对象
        Condition condition = pb.getCondition();
        // 条件之类别id
        int typeId = condition.getFoodType_id();
        // 条件之菜品名称
        String foodName = condition.getFoodName();

        StringBuffer sb = new StringBuffer();
        sb.append("select");
        sb.append("		f.id,");
        sb.append("		f.foodName,");
        sb.append("		f.price,");
        sb.append("		f.mPrice,");
        sb.append("		f.remark,");
        sb.append("		f.img,");
        sb.append("		f.foodType_id,");
        sb.append("		t.typeName ");
        sb.append("from ");
        sb.append("		food f, ");
        sb.append("		foodtype t ");
        sb.append("where 1=1");
        sb.append("		and f.foodType_id=t.id ");
        // 存储查询条件对应的值
        List<Object> list = new ArrayList<Object>();
        /*******拼接查询条件*********/
        // 类别id条件
        if (typeId > 0) {
            sb.append("	and f.foodType_id=?");
            list.add(typeId);  // 组装条件值
        }
        // 菜品名称
        if (foodName != null && !"".equals(foodName.trim())) {
            sb.append("  and f.foodName like ?");
            list.add(foodName);    // 组装条件值
        }

        /*********分页条件**********/
        sb.append(" LIMIT ?,?");


        /*****判断：当当前页< 1， 设置当前页为1；  当当前页>总页数，设置当前页为总页数******/
        // 先查询总记录数
        int totalCount = getTotalCount(pb);  //?
        // 设置分页bean参数之总记录数
        pb.setTotalCount(totalCount);

        if(pb.getCurrentPage() < 1) {
            pb.setCurrentPage(1);
        } else if (pb.getCurrentPage() > pb.getTotalPage()) {
            pb.setCurrentPage(pb.getTotalPage());
        }

        // 起始行
        int index = (pb.getCurrentPage() - 1) * pb.getPageCount();
        // 返回记录行
        int count = pb.getPageCount();

        list.add(index);		    // 组装条件值 - 起始行
        list.add(count);		    // 组装条件值 - 查询返回的行

        try {
            List<Food> pageData = JdbcUtils.getQueryRunner().query(sb.toString(), new BeanListHandler<Food>(Food.class), list.toArray());
            pb.setPageData(pageData);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int getTotalCount(PageBean<Food> pb) {
        // 获取条件对象
        Condition condition = pb.getCondition();
        // 条件之类别id
        int typeId = condition.getFoodType_id();
        // 条件之菜品名称
        String foodName = condition.getFoodName();

        StringBuffer sb = new StringBuffer();
        sb.append("select");
        sb.append("		count(*) ");
        sb.append("from ");
        sb.append("		food f, ");
        sb.append("		foodtype t ");
        sb.append("where 1=1");
        sb.append("		and f.foodType_id=t.id ");
        // 存储查询条件对应的值
        List<Object> list = new ArrayList<Object>();
        /*******拼接查询条件*********/
        // 类别id条件
        if (typeId > 0) {
            sb.append("	and f.foodType_id=?");
            list.add(typeId);  // 组装条件值
        }
        // 菜品名称
        if (foodName != null && !"".equals(foodName.trim())) {
            sb.append("  and f.foodName like ?");
            list.add(foodName);    // 组装条件值
        }

        try {
            // 查询
            Long num = JdbcUtils.getQueryRunner().query(sb.toString(), new ScalarHandler<Long>(),list.toArray());
            return num.intValue();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
